package com.xiyu.service.repository.office.space;

import com.xiyu.service.model.office.building.OfficeBuildingFetcher;
import com.xiyu.service.model.office.building.OfficeBuildingFloorFetcher;
import com.xiyu.service.model.office.building.OfficeBuildingUnitFetcher;
import com.xiyu.service.model.office.space.OfficeSpaceFetcher;
import com.xiyu.service.model.office.space.OfficeSpaceWorkerFetcher;
import com.xiyu.service.model.unit.rank.UnitRankFetcher;
import com.xiyu.service.model.unit.unit.UnitUnitFetcher;
import com.xiyu.service.model.unit.worker.UnitWorkerFetcher;
import com.xiyu.service.vo.office.space.SpacePageQueryInput;
import com.xiyu.service.model.office.space.OfficeSpace;
import com.xiyu.service.model.office.space.OfficeSpaceTable;
import org.babyfish.jimmer.spring.repository.JRepository;
import org.babyfish.jimmer.sql.ast.Expression;
import org.babyfish.jimmer.sql.ast.tuple.Tuple5;
import org.babyfish.jimmer.Page;

import java.math.BigDecimal;
import java.util.Optional;
import java.util.List;

public interface OfficeSpaceRepository extends JRepository<OfficeSpace, Long> {
    OfficeSpaceTable officeSpaceTable = OfficeSpaceTable.$;

    default Page<OfficeSpace> pageQuery(SpacePageQueryInput inputVO){
        return
            sql().createQuery(officeSpaceTable)
                    .where(officeSpaceTable.name().likeIf(inputVO.getName()))
                    .whereIf(inputVO.getUnitId() != null, officeSpaceTable.unitId().eq(inputVO.getUnitId()))
                    .whereIf(inputVO.getType() != null, officeSpaceTable.type().eq(inputVO.getType()))
                    .orderBy(officeSpaceTable.id().desc())
                    .select(officeSpaceTable.fetch(OfficeSpaceFetcher.$.allTableFields()
                            .building(OfficeBuildingFetcher.$.allTableFields())
                            .buildingFloor(OfficeBuildingFloorFetcher.$.allTableFields())
                            .buildingUnit(OfficeBuildingUnitFetcher.$.allTableFields())
                            .unit(UnitUnitFetcher.$.allTableFields())))
                    .fetchPage(inputVO.getPageNo() - 1, inputVO.getPageSize());

    }

    default Optional<OfficeSpace> findDetailById(Long id){
        return sql().createQuery(officeSpaceTable)
                .where(officeSpaceTable.id().eq(id))
                .select(officeSpaceTable.fetch(OfficeSpaceFetcher.$.allTableFields()
                        .building(OfficeBuildingFetcher.$.allTableFields())
                        .spaceWorkers(OfficeSpaceWorkerFetcher.$.allTableFields()
                                .worker(UnitWorkerFetcher.$.allTableFields()
                                        .rank(UnitRankFetcher.$.allTableFields())))))
                .fetchOptional();
    }

    default List<Tuple5<Integer, Long, BigDecimal, Long, BigDecimal>> findGroupTypeInfo(){
        return sql().createQuery(officeSpaceTable)
                .groupBy(officeSpaceTable.type())
                .select(officeSpaceTable.type(), officeSpaceTable.id().count(),
                        officeSpaceTable.area().sum(),
                        Expression.any().sql(Long.class,"SUM(IF(%e = 1, 1, 0))",officeSpaceTable.status()),
                        Expression.any().sql(BigDecimal.class, "SUM(IF(%e = 1, %e, 0))", new Expression[] { officeSpaceTable.status(), officeSpaceTable.area()})
                )
                .execute();
    }


}